package com.luyuan.crm.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ExcelSupport {
    private static final Log logger = LogFactory.getLog(ExcelSupport.class);

    static String sql = "update lyt_customer c set c.support_id = (select uuid from sct_user u where u.username = ''{0}'') where c.mobile like ''%{1}%'' and discriminator = ''member'';";

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/luyuan", "root", "admin");
            Statement state = conn.createStatement();
            PreparedStatement ps = conn
                    .prepareStatement("update lyt_customer c set c.support_id = (select uuid from sct_user u where u.username = ?) where c.mobile like ? and discriminator = 'member';");

            Workbook book = Workbook.getWorkbook(ExcelSupport.class.getResourceAsStream("support.xls")); // 获得第一个工作表对象
            Sheet sheet = book.getSheet(0); // 得到第一列第一行的单元格

            for (int i = 1; i < sheet.getRows(); i++) {
                System.out.println(i);
                Cell cellUsername = sheet.getCell(0, i);
                Cell cellPhone = sheet.getCell(2, i);

                String username = cellUsername.getContents();
                String phone = cellPhone.getContents();

                String[] phones = phone.split("/|\\s|或");
                if (phones.length > 1) {
                    // System.out.println(getSql(username, ps[0]));
                    // System.out.println(getSql(username, ps[1]));
                    // System.out.println(ps[0]);
                    if (phones[1].trim().length() == 0) {
                        // System.out.println(ps[2]);
                        String r = getSql(state, ps, username, phones[2]);
                        if (r != null) {
                            logger.info(r);
                        }
                    }
                    else {
                        // System.out.println(ps[1]);
                        String r = getSql(state, ps, username, phones[1]);
                        if (r != null) {
                            logger.info(r);
                        }
                    }
                }
                else {
                    String r = getSql(state, ps, username, phone);
                    if (r != null) {
                        logger.info(r);
                    }
                }
            }
            // System.out.println(MessageFormat.format(sql,
            // cellUsername.getContents(), cellPhone.getContents()));
            book.close();

            state.close();
            conn.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    private static String getSql(Statement state, PreparedStatement ps, String username, String phone)
            throws SQLException {
        ps.setString(1, username);
        ps.setString(2, "%" + phone + "%");
        int i = ps.executeUpdate();
        if (i == 0) {
            return "手机号不存在，更新失败。会员号：" + username + ", 手机：" + phone;
        }
        else if (i > 1) {
            return "手机重复，会员号：" + username + ", 手机：" + phone;
        }
        ResultSet rs = state.executeQuery("SELECT c.support_id FROM lyt_customer c where c.mobile like '%" + phone
                + "%'");
        if (rs.next()) {
            String p = rs.getString(1);
            if (p == null || p.trim().length() == 0) {
                return "售后编号不存在，更新失败。" + "会员号：" + username + ", 手机：" + phone;
            }
        }
        return null;
    }
}
